﻿

/*===========================Start Contracts Select PROCEDURE===========================*/
IF NOT  EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_Contracts_SELECT') AND type in (N'P', N'PC'))
	BEGIN
		EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_Contracts_SELECT AS'
	END 
EXEC dbo.sp_executesql @statement = N'Alter PROCEDURE dbo.SP_Contracts_SELECT
@StartIndex INT = NULL,
@lenght INT =  NULL ,
@OrderColume nvarchar(256) = NULL
, @ContractId Int   =  NULL 
, @CompanyName NVarChar (50)  =  NULL 
, @ContracrtDate DateTime   =  NULL 
, @FromDate Date   =  NULL 
, @ToDate Date   =  NULL 
, @MaxLimit Int   =  NULL 
, @Precentage Decimal   =  NULL 
	AS
			WITH CTE_Contracts AS
			( 
			SELECT
 							ContractId,
								CompanyName,
								ContracrtDate,
								FromDate,
								ToDate,
								MaxLimit,
								Precentage,
							ROW_NUMBER() OVER (ORDER BY 
        	CASE 
									WHEN @OrderColume=''ContractId'' THEN ContractId
						 
						WHEN @OrderColume=''CompanyName'' THEN CompanyName
												WHEN @OrderColume=''ContracrtDate'' THEN ContracrtDate
												WHEN @OrderColume=''FromDate'' THEN FromDate
												WHEN @OrderColume=''ToDate'' THEN ToDate
												WHEN @OrderColume=''MaxLimit'' THEN MaxLimit
												WHEN @OrderColume=''Precentage'' THEN Precentage
									ELSE 
							    ContractId
					        	END
			) AS "RowNumber"
  			FROM Contracts 
			where 
								(ContractId = @ContractId OR @ContractId Is Null)
										AND(CompanyName LIKE @CompanyName OR @CompanyName Is Null)
										AND(ContracrtDate = @ContracrtDate OR @ContracrtDate Is Null)
										AND(FromDate = @FromDate OR @FromDate Is Null)
										AND(ToDate = @ToDate OR @ToDate Is Null)
										AND(MaxLimit = @MaxLimit OR @MaxLimit Is Null)
										AND(Precentage = @Precentage OR @Precentage Is Null)
								)
			SELECT  *
			FROM CTE_Contracts
			WHERE  (RowNumber >= @StartIndex OR @StartIndex Is Null) AND (RowNumber <= @StartIndex+@lenght OR (@StartIndex Is Null and @lenght Is Null))
			Return (select @@ROWCOUNT)'
			PRINT 'dbo.SP_Contracts_SELECT done'
			go
 /*===========================End Contracts Select PROCEDURE===========================*/
/*===========================Start Contracts Insert PROCEDURE===========================*/
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_Contracts_Insert') AND type in (N'P', N'PC'))
	Begin
		EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_Contracts_Insert AS'
	END
	EXEC dbo.sp_executesql @statement = N'Alter PROCEDURE dbo.SP_Contracts_Insert
	@CompanyName NVarChar (50)  =  NULL 
		, @ContracrtDate DateTime   =  NULL 
		, @FromDate Date   =  NULL 
		, @ToDate Date   =  NULL 
		, @MaxLimit Int   =  NULL 
		, @Precentage Decimal   =  NULL 
		AS
			INSERT INTO Contracts
			(
									CompanyName
												,ContracrtDate
												,FromDate
												,ToDate
												,MaxLimit
												,Precentage
									)
			Values(
									@CompanyName
												,@ContracrtDate
												,@FromDate
												,@ToDate
												,@MaxLimit
												,@Precentage
						)
			return @@identity'
			PRINT 'dbo.SP_Contracts_Insert done'
			go
/*===========================End Contracts Insert PROCEDURE===========================*/
/*===========================Start Contracts Update PROCEDURE=========================*/
		IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_Contracts_UPDATE') AND type in (N'P', N'PC'))
		Begin
			EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_Contracts_UPDATE AS'
		end
		EXEC dbo.sp_executesql @statement = N'ALTER PROCEDURE dbo.SP_Contracts_UPDATE
												@ContractId Int 							  =  NULL 
														, @CompanyName NVarChar 							(50)  =  NULL 
														, @ContracrtDate DateTime 							  =  NULL 
														, @FromDate Date 							  =  NULL 
														, @ToDate Date 							  =  NULL 
														, @MaxLimit Int 							  =  NULL 
														, @Precentage Decimal 							  =  NULL 
												AS
					UPDATE Contracts SET
													CompanyName=@CompanyName 
																,ContracrtDate=@ContracrtDate
																,FromDate=@FromDate
																,ToDate=@ToDate
																,MaxLimit=@MaxLimit
																,Precentage=@Precentage
													Where 	
												ContractId=@ContractId
							'					

					PRINT 'dbo.SP_Contracts_Update done'
					go
				/*===========================End Contracts Update PROCEDURE=========================*/
 /*===========================Start Contracts Delete PROCEDURE===========================*/
 IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_Contracts_Delete') AND type in (N'P', N'PC'))
	BEGIN
		EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_Contracts_Delete AS'
	END
EXEC dbo.sp_executesql @statement = N'Alter PROCEDURE dbo.SP_Contracts_Delete
		@ContractId Int 		  =  NULL 
					AS
			Delete From Contracts
			Where 	
								(ContractId=@ContractId)
					'
			PRINT 'dbo.SP_Contracts_Delete done'
			go
/*===========================End Contracts Delete PROCEDURE===========================*/
/*===========================Start PatientOrganes Select PROCEDURE===========================*/
IF NOT  EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_PatientOrganes_SELECT') AND type in (N'P', N'PC'))
	BEGIN
		EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_PatientOrganes_SELECT AS'
	END 
EXEC dbo.sp_executesql @statement = N'Alter PROCEDURE dbo.SP_PatientOrganes_SELECT
@StartIndex INT = NULL,
@lenght INT =  NULL ,
@OrderColume nvarchar(256) = NULL
, @PateintOrganesId Int   =  NULL 
, @OrganesName NVarChar (100)  =  NULL 
	AS
			WITH CTE_PatientOrganes AS
			( 
			SELECT
 							PateintOrganesId,
								OrganesName,
							ROW_NUMBER() OVER (ORDER BY 
        	CASE 
									WHEN @OrderColume=''PateintOrganesId'' THEN PateintOrganesId
						 
						WHEN @OrderColume=''OrganesName'' THEN OrganesName
									ELSE 
							    PateintOrganesId
					        	END
			) AS "RowNumber"
  			FROM PatientOrganes 
			where 
								(PateintOrganesId = @PateintOrganesId OR @PateintOrganesId Is Null)
										AND(OrganesName LIKE @OrganesName OR @OrganesName Is Null)
								)
			SELECT  *
			FROM CTE_PatientOrganes
			WHERE  (RowNumber >= @StartIndex OR @StartIndex Is Null) AND (RowNumber <= @StartIndex+@lenght OR (@StartIndex Is Null and @lenght Is Null))
			Return (select @@ROWCOUNT)'
			PRINT 'dbo.SP_PatientOrganes_SELECT done'
			go
 /*===========================End PatientOrganes Select PROCEDURE===========================*/
/*===========================Start PatientOrganes Insert PROCEDURE===========================*/
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_PatientOrganes_Insert') AND type in (N'P', N'PC'))
	Begin
		EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_PatientOrganes_Insert AS'
	END
	EXEC dbo.sp_executesql @statement = N'Alter PROCEDURE dbo.SP_PatientOrganes_Insert
	@OrganesName NVarChar (100)  =  NULL 
		AS
			INSERT INTO PatientOrganes
			(
									OrganesName
									)
			Values(
									@OrganesName
						)
			return @@identity'
			PRINT 'dbo.SP_PatientOrganes_Insert done'
			go
/*===========================End PatientOrganes Insert PROCEDURE===========================*/
/*===========================Start PatientOrganes Update PROCEDURE=========================*/
		IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_PatientOrganes_UPDATE') AND type in (N'P', N'PC'))
		Begin
			EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_PatientOrganes_UPDATE AS'
		end
		EXEC dbo.sp_executesql @statement = N'ALTER PROCEDURE dbo.SP_PatientOrganes_UPDATE
												@PateintOrganesId Int 							  =  NULL 
														, @OrganesName NVarChar 							(100)  =  NULL 
												AS
					UPDATE PatientOrganes SET
													OrganesName=@OrganesName 
													Where 	
												PateintOrganesId=@PateintOrganesId
							'					

					PRINT 'dbo.SP_PatientOrganes_Update done'
					go
				/*===========================End PatientOrganes Update PROCEDURE=========================*/
 /*===========================Start PatientOrganes Delete PROCEDURE===========================*/
 IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_PatientOrganes_Delete') AND type in (N'P', N'PC'))
	BEGIN
		EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_PatientOrganes_Delete AS'
	END
EXEC dbo.sp_executesql @statement = N'Alter PROCEDURE dbo.SP_PatientOrganes_Delete
		@PateintOrganesId Int 		  =  NULL 
					AS
			Delete From PatientOrganes
			Where 	
								(PateintOrganesId=@PateintOrganesId)
					'
			PRINT 'dbo.SP_PatientOrganes_Delete done'
			go
/*===========================End PatientOrganes Delete PROCEDURE===========================*/
/*===========================Start RadiologyDevice Select PROCEDURE===========================*/
IF NOT  EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_RadiologyDevice_SELECT') AND type in (N'P', N'PC'))
	BEGIN
		EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_RadiologyDevice_SELECT AS'
	END 
EXEC dbo.sp_executesql @statement = N'Alter PROCEDURE dbo.SP_RadiologyDevice_SELECT
@StartIndex INT = NULL,
@lenght INT =  NULL ,
@OrderColume nvarchar(256) = NULL
, @DeviceCode NChar (10)  =  NULL 
, @DeviceName NVarChar (100)  =  NULL 
, @Description NVarChar (500)  =  NULL 
, @Dep_id Int   =  NULL 
	AS
			WITH CTE_RadiologyDevice AS
			( 
			SELECT
 							DeviceCode,
								DeviceName,
								Description,
								Dep_id,
							ROW_NUMBER() OVER (ORDER BY 
        	CASE 
			 
						WHEN @OrderColume=''DeviceCode'' THEN DeviceCode
						 
						WHEN @OrderColume=''DeviceName'' THEN DeviceName
						 
						WHEN @OrderColume=''Description'' THEN Description
												WHEN @OrderColume=''Dep_id'' THEN Dep_id
									ELSE 
			 
				    DeviceCode
					        	END
			) AS "RowNumber"
  			FROM RadiologyDevice 
			where 
								(DeviceCode LIKE @DeviceCode OR @DeviceCode Is Null)
										AND(DeviceName LIKE @DeviceName OR @DeviceName Is Null)
										AND(Description LIKE @Description OR @Description Is Null)
										AND(Dep_id = @Dep_id OR @Dep_id Is Null)
								)
			SELECT  *
			FROM CTE_RadiologyDevice
			WHERE  (RowNumber >= @StartIndex OR @StartIndex Is Null) AND (RowNumber <= @StartIndex+@lenght OR (@StartIndex Is Null and @lenght Is Null))
			Return (select @@ROWCOUNT)'
			PRINT 'dbo.SP_RadiologyDevice_SELECT done'
			go
 /*===========================End RadiologyDevice Select PROCEDURE===========================*/
/*===========================Start RadiologyDevice Insert PROCEDURE===========================*/
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_RadiologyDevice_Insert') AND type in (N'P', N'PC'))
	Begin
		EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_RadiologyDevice_Insert AS'
	END
	EXEC dbo.sp_executesql @statement = N'Alter PROCEDURE dbo.SP_RadiologyDevice_Insert
	@DeviceCode NChar (10)  =  NULL 
		, @DeviceName NVarChar (100)  =  NULL 
		, @Description NVarChar (500)  =  NULL 
		, @Dep_id Int   =  NULL 
		AS
			INSERT INTO RadiologyDevice
			(
									DeviceCode
												,DeviceName
												,Description
												,Dep_id
									)
			Values(
									@DeviceCode
												,@DeviceName
												,@Description
												,@Dep_id
						)
			return @@identity'
			PRINT 'dbo.SP_RadiologyDevice_Insert done'
			go
/*===========================End RadiologyDevice Insert PROCEDURE===========================*/
/*===========================Start RadiologyDevice Update PROCEDURE=========================*/
		IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_RadiologyDevice_UPDATE') AND type in (N'P', N'PC'))
		Begin
			EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_RadiologyDevice_UPDATE AS'
		end
		EXEC dbo.sp_executesql @statement = N'ALTER PROCEDURE dbo.SP_RadiologyDevice_UPDATE
												@DeviceCode NChar 							(10)  =  NULL 
														, @DeviceName NVarChar 							(100)  =  NULL 
														, @Description NVarChar 							(500)  =  NULL 
														, @Dep_id Int 							  =  NULL 
												AS
					UPDATE RadiologyDevice SET
													DeviceName=@DeviceName 
																,Description=@Description
																,Dep_id=@Dep_id
													Where 	
												DeviceCode=@DeviceCode
							'					

					PRINT 'dbo.SP_RadiologyDevice_Update done'
					go
				/*===========================End RadiologyDevice Update PROCEDURE=========================*/
 /*===========================Start RadiologyDevice Delete PROCEDURE===========================*/
 IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_RadiologyDevice_Delete') AND type in (N'P', N'PC'))
	BEGIN
		EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_RadiologyDevice_Delete AS'
	END
EXEC dbo.sp_executesql @statement = N'Alter PROCEDURE dbo.SP_RadiologyDevice_Delete
		@DeviceCode NChar 		(10)  =  NULL 
					AS
			Delete From RadiologyDevice
			Where 	
								(DeviceCode=@DeviceCode)
					'
			PRINT 'dbo.SP_RadiologyDevice_Delete done'
			go
/*===========================End RadiologyDevice Delete PROCEDURE===========================*/
/*===========================Start RadiologyType Select PROCEDURE===========================*/
IF NOT  EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_RadiologyType_SELECT') AND type in (N'P', N'PC'))
	BEGIN
		EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_RadiologyType_SELECT AS'
	END 
EXEC dbo.sp_executesql @statement = N'Alter PROCEDURE dbo.SP_RadiologyType_SELECT
@StartIndex INT = NULL,
@lenght INT =  NULL ,
@OrderColume nvarchar(256) = NULL
, @RadiologyCode NChar (10)  =  NULL 
, @Description NVarChar (500)  =  NULL 
, @Price Money   =  NULL 
, @PatientGander Bit   =  NULL 
, @PatientOrgansId Int   =  NULL 
, @DeviceCode NChar (10)  =  NULL 
	AS
			WITH CTE_RadiologyType AS
			( 
			SELECT
 							RadiologyCode,
								Description,
								Price,
								PatientGander,
								PatientOrgansId,
								DeviceCode,
							ROW_NUMBER() OVER (ORDER BY 
        	CASE 
			 
						WHEN @OrderColume=''RadiologyCode'' THEN RadiologyCode
						 
						WHEN @OrderColume=''Description'' THEN Description
												WHEN @OrderColume=''Price'' THEN Price
												WHEN @OrderColume=''PatientGander'' THEN PatientGander
												WHEN @OrderColume=''PatientOrgansId'' THEN PatientOrgansId
						 
						WHEN @OrderColume=''DeviceCode'' THEN DeviceCode
									ELSE 
			 
				    RadiologyCode
					        	END
			) AS "RowNumber"
  			FROM RadiologyType 
			where 
								(RadiologyCode LIKE @RadiologyCode OR @RadiologyCode Is Null)
										AND(Description LIKE @Description OR @Description Is Null)
										AND(Price = @Price OR @Price Is Null)
										AND(PatientGander = @PatientGander OR @PatientGander Is Null)
										AND(PatientOrgansId = @PatientOrgansId OR @PatientOrgansId Is Null)
										AND(DeviceCode LIKE @DeviceCode OR @DeviceCode Is Null)
								)
			SELECT  *
			FROM CTE_RadiologyType
			WHERE  (RowNumber >= @StartIndex OR @StartIndex Is Null) AND (RowNumber <= @StartIndex+@lenght OR (@StartIndex Is Null and @lenght Is Null))
			Return (select @@ROWCOUNT)'
			PRINT 'dbo.SP_RadiologyType_SELECT done'
			go
 /*===========================End RadiologyType Select PROCEDURE===========================*/
/*===========================Start RadiologyType Insert PROCEDURE===========================*/
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_RadiologyType_Insert') AND type in (N'P', N'PC'))
	Begin
		EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_RadiologyType_Insert AS'
	END
	EXEC dbo.sp_executesql @statement = N'Alter PROCEDURE dbo.SP_RadiologyType_Insert
	@RadiologyCode NChar (10)  =  NULL 
		, @Description NVarChar (500)  =  NULL 
		, @Price Money   =  NULL 
		, @PatientGander Bit   =  NULL 
		, @PatientOrgansId Int   =  NULL 
		, @DeviceCode NChar (10)  =  NULL 
		AS
			INSERT INTO RadiologyType
			(
									RadiologyCode
												,Description
												,Price
												,PatientGander
												,PatientOrgansId
												,DeviceCode
									)
			Values(
									@RadiologyCode
												,@Description
												,@Price
												,@PatientGander
												,@PatientOrgansId
												,@DeviceCode
						)
			return @@identity'
			PRINT 'dbo.SP_RadiologyType_Insert done'
			go
/*===========================End RadiologyType Insert PROCEDURE===========================*/
/*===========================Start RadiologyType Update PROCEDURE=========================*/
		IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_RadiologyType_UPDATE') AND type in (N'P', N'PC'))
		Begin
			EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_RadiologyType_UPDATE AS'
		end
		EXEC dbo.sp_executesql @statement = N'ALTER PROCEDURE dbo.SP_RadiologyType_UPDATE
												@RadiologyCode NChar 							(10)  =  NULL 
														, @Description NVarChar 							(500)  =  NULL 
														, @Price Money 							  =  NULL 
														, @PatientGander Bit 							  =  NULL 
														, @PatientOrgansId Int 							  =  NULL 
														, @DeviceCode NChar 							(10)  =  NULL 
												AS
					UPDATE RadiologyType SET
													Description=@Description 
																,Price=@Price
																,PatientGander=@PatientGander
																,PatientOrgansId=@PatientOrgansId
																,DeviceCode=@DeviceCode
													Where 	
												RadiologyCode=@RadiologyCode
							'					

					PRINT 'dbo.SP_RadiologyType_Update done'
					go
				/*===========================End RadiologyType Update PROCEDURE=========================*/
 /*===========================Start RadiologyType Delete PROCEDURE===========================*/
 IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_RadiologyType_Delete') AND type in (N'P', N'PC'))
	BEGIN
		EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_RadiologyType_Delete AS'
	END
EXEC dbo.sp_executesql @statement = N'Alter PROCEDURE dbo.SP_RadiologyType_Delete
		@RadiologyCode NChar 		(10)  =  NULL 
					AS
			Delete From RadiologyType
			Where 	
								(RadiologyCode=@RadiologyCode)
					'
			PRINT 'dbo.SP_RadiologyType_Delete done'
			go
/*===========================End RadiologyType Delete PROCEDURE===========================*/
/*===========================Start RadiologyRequest Select PROCEDURE===========================*/
IF NOT  EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_RadiologyRequest_SELECT') AND type in (N'P', N'PC'))
	BEGIN
		EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_RadiologyRequest_SELECT AS'
	END 
EXEC dbo.sp_executesql @statement = N'Alter PROCEDURE dbo.SP_RadiologyRequest_SELECT
@StartIndex INT = NULL,
@lenght INT =  NULL ,
@OrderColume nvarchar(256) = NULL
, @RequestId Int   =  NULL 
, @RequestDate DateTime   =  NULL 
, @ReservationDate DateTime   =  NULL 
, @PatientPay Money   =  NULL 
, @PayResetNum Int   =  NULL 
, @PatientSituation Bit   =  NULL 
, @RequestStatues Bit   =  NULL 
, @RadiologyCode NChar (10)  =  NULL 
, @PatientID Int   =  NULL 
, @ReceptionEmpID Int   =  NULL 
	AS
			WITH CTE_RadiologyRequest AS
			( 
			SELECT
 							RequestId,
								RequestDate,
								ReservationDate,
								PatientPay,
								PayResetNum,
								PatientSituation,
								RequestStatues,
								RadiologyCode,
								PatientID,
								ReceptionEmpID,
							ROW_NUMBER() OVER (ORDER BY 
        	CASE 
									WHEN @OrderColume=''RequestId'' THEN RequestId
												WHEN @OrderColume=''RequestDate'' THEN RequestDate
												WHEN @OrderColume=''ReservationDate'' THEN ReservationDate
												WHEN @OrderColume=''PatientPay'' THEN PatientPay
												WHEN @OrderColume=''PayResetNum'' THEN PayResetNum
												WHEN @OrderColume=''PatientSituation'' THEN PatientSituation
												WHEN @OrderColume=''RequestStatues'' THEN RequestStatues
						 
						WHEN @OrderColume=''RadiologyCode'' THEN RadiologyCode
												WHEN @OrderColume=''PatientID'' THEN PatientID
												WHEN @OrderColume=''ReceptionEmpID'' THEN ReceptionEmpID
									ELSE 
							    RequestId
					        	END
			) AS "RowNumber"
  			FROM RadiologyRequest 
			where 
								(RequestId = @RequestId OR @RequestId Is Null)
										AND(RequestDate = @RequestDate OR @RequestDate Is Null)
										AND(ReservationDate = @ReservationDate OR @ReservationDate Is Null)
										AND(PatientPay = @PatientPay OR @PatientPay Is Null)
										AND(PayResetNum = @PayResetNum OR @PayResetNum Is Null)
										AND(PatientSituation = @PatientSituation OR @PatientSituation Is Null)
										AND(RequestStatues = @RequestStatues OR @RequestStatues Is Null)
										AND(RadiologyCode LIKE @RadiologyCode OR @RadiologyCode Is Null)
										AND(PatientID = @PatientID OR @PatientID Is Null)
										AND(ReceptionEmpID = @ReceptionEmpID OR @ReceptionEmpID Is Null)
								)
			SELECT  *
			FROM CTE_RadiologyRequest
			WHERE  (RowNumber >= @StartIndex OR @StartIndex Is Null) AND (RowNumber <= @StartIndex+@lenght OR (@StartIndex Is Null and @lenght Is Null))
			Return (select @@ROWCOUNT)'
			PRINT 'dbo.SP_RadiologyRequest_SELECT done'
			go
 /*===========================End RadiologyRequest Select PROCEDURE===========================*/
/*===========================Start RadiologyRequest Insert PROCEDURE===========================*/
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_RadiologyRequest_Insert') AND type in (N'P', N'PC'))
	Begin
		EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_RadiologyRequest_Insert AS'
	END
	EXEC dbo.sp_executesql @statement = N'Alter PROCEDURE dbo.SP_RadiologyRequest_Insert
	@RequestDate DateTime   =  NULL 
		, @ReservationDate DateTime   =  NULL 
		, @PatientPay Money   =  NULL 
		, @PayResetNum Int   =  NULL 
		, @PatientSituation Bit   =  NULL 
		, @RequestStatues Bit   =  NULL 
		, @RadiologyCode NChar (10)  =  NULL 
		, @PatientID Int   =  NULL 
		, @ReceptionEmpID Int   =  NULL 
		AS
			INSERT INTO RadiologyRequest
			(
									RequestDate
												,ReservationDate
												,PatientPay
												,PayResetNum
												,PatientSituation
												,RequestStatues
												,RadiologyCode
												,PatientID
												,ReceptionEmpID
									)
			Values(
									@RequestDate
												,@ReservationDate
												,@PatientPay
												,@PayResetNum
												,@PatientSituation
												,@RequestStatues
												,@RadiologyCode
												,@PatientID
												,@ReceptionEmpID
						)
			return @@identity'
			PRINT 'dbo.SP_RadiologyRequest_Insert done'
			go
/*===========================End RadiologyRequest Insert PROCEDURE===========================*/
/*===========================Start RadiologyRequest Update PROCEDURE=========================*/
		IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_RadiologyRequest_UPDATE') AND type in (N'P', N'PC'))
		Begin
			EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_RadiologyRequest_UPDATE AS'
		end
		EXEC dbo.sp_executesql @statement = N'ALTER PROCEDURE dbo.SP_RadiologyRequest_UPDATE
												@RequestId Int 							  =  NULL 
														, @RequestDate DateTime 							  =  NULL 
														, @ReservationDate DateTime 							  =  NULL 
														, @PatientPay Money 							  =  NULL 
														, @PayResetNum Int 							  =  NULL 
														, @PatientSituation Bit 							  =  NULL 
														, @RequestStatues Bit 							  =  NULL 
														, @RadiologyCode NChar 							(10)  =  NULL 
														, @PatientID Int 							  =  NULL 
														, @ReceptionEmpID Int 							  =  NULL 
												AS
					UPDATE RadiologyRequest SET
													RequestDate=@RequestDate 
																,ReservationDate=@ReservationDate
																,PatientPay=@PatientPay
																,PayResetNum=@PayResetNum
																,PatientSituation=@PatientSituation
																,RequestStatues=@RequestStatues
																,RadiologyCode=@RadiologyCode
																,PatientID=@PatientID
																,ReceptionEmpID=@ReceptionEmpID
													Where 	
												RequestId=@RequestId
							'					

					PRINT 'dbo.SP_RadiologyRequest_Update done'
					go
				/*===========================End RadiologyRequest Update PROCEDURE=========================*/
 /*===========================Start RadiologyRequest Delete PROCEDURE===========================*/
 IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_RadiologyRequest_Delete') AND type in (N'P', N'PC'))
	BEGIN
		EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_RadiologyRequest_Delete AS'
	END
EXEC dbo.sp_executesql @statement = N'Alter PROCEDURE dbo.SP_RadiologyRequest_Delete
		@RequestId Int 		  =  NULL 
					AS
			Delete From RadiologyRequest
			Where 	
								(RequestId=@RequestId)
					'
			PRINT 'dbo.SP_RadiologyRequest_Delete done'
			go
/*===========================End RadiologyRequest Delete PROCEDURE===========================*/
/*===========================Start RadiologyImage Select PROCEDURE===========================*/
IF NOT  EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_RadiologyImage_SELECT') AND type in (N'P', N'PC'))
	BEGIN
		EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_RadiologyImage_SELECT AS'
	END 
EXEC dbo.sp_executesql @statement = N'Alter PROCEDURE dbo.SP_RadiologyImage_SELECT
@StartIndex INT = NULL,
@lenght INT =  NULL ,
@OrderColume nvarchar(256) = NULL
, @ImageID Int   =  NULL 
, @RequestId Int   =  NULL 
, @ImagePath NVarChar (200)  =  NULL 
, @ImageDate DateTime   =  NULL 
, @EmpID Int   =  NULL 
, @Histogram VarBinary (Max)  =  NULL 
	AS
			WITH CTE_RadiologyImage AS
			( 
			SELECT
 							ImageID,
								RequestId,
								ImagePath,
								ImageDate,
								EmpID,
								Histogram,
							ROW_NUMBER() OVER (ORDER BY 
        	CASE 
									WHEN @OrderColume=''ImageID'' THEN ImageID
												WHEN @OrderColume=''RequestId'' THEN RequestId
						 
						WHEN @OrderColume=''ImagePath'' THEN ImagePath
												WHEN @OrderColume=''ImageDate'' THEN ImageDate
												WHEN @OrderColume=''EmpID'' THEN EmpID
						 
						WHEN @OrderColume=''Histogram'' THEN Histogram
									ELSE 
							    ImageID
					        	END
			) AS "RowNumber"
  			FROM RadiologyImage 
			where 
								(ImageID = @ImageID OR @ImageID Is Null)
										AND(RequestId = @RequestId OR @RequestId Is Null)
										AND(ImagePath LIKE @ImagePath OR @ImagePath Is Null)
										AND(ImageDate = @ImageDate OR @ImageDate Is Null)
										AND(EmpID = @EmpID OR @EmpID Is Null)
										AND(Histogram = @Histogram OR @Histogram Is Null)
								)
			SELECT  *
			FROM CTE_RadiologyImage
			WHERE  (RowNumber >= @StartIndex OR @StartIndex Is Null) AND (RowNumber <= @StartIndex+@lenght OR (@StartIndex Is Null and @lenght Is Null))
			Return (select @@ROWCOUNT)'
			PRINT 'dbo.SP_RadiologyImage_SELECT done'
			go
 /*===========================End RadiologyImage Select PROCEDURE===========================*/
/*===========================Start RadiologyImage Insert PROCEDURE===========================*/
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_RadiologyImage_Insert') AND type in (N'P', N'PC'))
	Begin
		EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_RadiologyImage_Insert AS'
	END
	EXEC dbo.sp_executesql @statement = N'Alter PROCEDURE dbo.SP_RadiologyImage_Insert
	@RequestId Int   =  NULL 
		, @ImagePath NVarChar (200)  =  NULL 
		, @ImageDate DateTime   =  NULL 
		, @EmpID Int   =  NULL 
		, @Histogram VarBinary (Max)  =  NULL 
		AS
			INSERT INTO RadiologyImage
			(
									RequestId
												,ImagePath
												,ImageDate
												,EmpID
												,Histogram
									)
			Values(
									@RequestId
												,@ImagePath
												,@ImageDate
												,@EmpID
												,@Histogram
						)
			return @@identity'
			PRINT 'dbo.SP_RadiologyImage_Insert done'
			go
/*===========================End RadiologyImage Insert PROCEDURE===========================*/
/*===========================Start RadiologyImage Update PROCEDURE=========================*/
		IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_RadiologyImage_UPDATE') AND type in (N'P', N'PC'))
		Begin
			EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_RadiologyImage_UPDATE AS'
		end
		EXEC dbo.sp_executesql @statement = N'ALTER PROCEDURE dbo.SP_RadiologyImage_UPDATE
												@ImageID Int 							  =  NULL 
														, @RequestId Int 							  =  NULL 
														, @ImagePath NVarChar 							(200)  =  NULL 
														, @ImageDate DateTime 							  =  NULL 
														, @EmpID Int 							  =  NULL 
														, @Histogram VarBinary (Max)							  =  NULL 
												AS
					UPDATE RadiologyImage SET
													RequestId=@RequestId 
																,ImagePath=@ImagePath
																,ImageDate=@ImageDate
																,EmpID=@EmpID
																,Histogram=@Histogram
													Where 	
												ImageID=@ImageID
							'					

					PRINT 'dbo.SP_RadiologyImage_Update done'
					go
				/*===========================End RadiologyImage Update PROCEDURE=========================*/
 /*===========================Start RadiologyImage Delete PROCEDURE===========================*/
 IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_RadiologyImage_Delete') AND type in (N'P', N'PC'))
	BEGIN
		EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_RadiologyImage_Delete AS'
	END
EXEC dbo.sp_executesql @statement = N'Alter PROCEDURE dbo.SP_RadiologyImage_Delete
		@ImageID Int 		  =  NULL 
					AS
			Delete From RadiologyImage
			Where 	
								(ImageID=@ImageID)
					'
			PRINT 'dbo.SP_RadiologyImage_Delete done'
			go
/*===========================End RadiologyImage Delete PROCEDURE===========================*/
/*===========================Start TechnicianDevice Select PROCEDURE===========================*/
IF NOT  EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_TechnicianDevice_SELECT') AND type in (N'P', N'PC'))
	BEGIN
		EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_TechnicianDevice_SELECT AS'
	END 
EXEC dbo.sp_executesql @statement = N'Alter PROCEDURE dbo.SP_TechnicianDevice_SELECT
@StartIndex INT = NULL,
@lenght INT =  NULL ,
@OrderColume nvarchar(256) = NULL
, @TechnicianID Int   =  NULL 
, @DeviceCode NChar (10)  =  NULL 
, @StratWorkingDate DateTime   =  NULL 
	AS
			WITH CTE_TechnicianDevice AS
			( 
			SELECT
 							TechnicianID,
								DeviceCode,
								StratWorkingDate,
							ROW_NUMBER() OVER (ORDER BY 
        	CASE 
									WHEN @OrderColume=''TechnicianID'' THEN TechnicianID
						 
						WHEN @OrderColume=''DeviceCode'' THEN DeviceCode
												WHEN @OrderColume=''StratWorkingDate'' THEN StratWorkingDate
									ELSE 
							    TechnicianID
					        	END
			) AS "RowNumber"
  			FROM TechnicianDevice 
			where 
								(TechnicianID = @TechnicianID OR @TechnicianID Is Null)
										AND(DeviceCode LIKE @DeviceCode OR @DeviceCode Is Null)
										AND(StratWorkingDate = @StratWorkingDate OR @StratWorkingDate Is Null)
								)
			SELECT  *
			FROM CTE_TechnicianDevice
			WHERE  (RowNumber >= @StartIndex OR @StartIndex Is Null) AND (RowNumber <= @StartIndex+@lenght OR (@StartIndex Is Null and @lenght Is Null))
			Return (select @@ROWCOUNT)'
			PRINT 'dbo.SP_TechnicianDevice_SELECT done'
			go
 /*===========================End TechnicianDevice Select PROCEDURE===========================*/
/*===========================Start TechnicianDevice Insert PROCEDURE===========================*/
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_TechnicianDevice_Insert') AND type in (N'P', N'PC'))
	Begin
		EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_TechnicianDevice_Insert AS'
	END
	EXEC dbo.sp_executesql @statement = N'Alter PROCEDURE dbo.SP_TechnicianDevice_Insert
	@TechnicianID Int   =  NULL 
		, @DeviceCode NChar (10)  =  NULL 
		, @StratWorkingDate DateTime   =  NULL 
		AS
			INSERT INTO TechnicianDevice
			(
									TechnicianID
												,DeviceCode
												,StratWorkingDate
									)
			Values(
									@TechnicianID
												,@DeviceCode
												,@StratWorkingDate
						)
			return @@identity'
			PRINT 'dbo.SP_TechnicianDevice_Insert done'
			go
/*===========================End TechnicianDevice Insert PROCEDURE===========================*/
/*===========================Start TechnicianDevice Update PROCEDURE=========================*/
		IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_TechnicianDevice_UPDATE') AND type in (N'P', N'PC'))
		Begin
			EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_TechnicianDevice_UPDATE AS'
		end
		EXEC dbo.sp_executesql @statement = N'ALTER PROCEDURE dbo.SP_TechnicianDevice_UPDATE
												@TechnicianID Int 							  =  NULL 
														, @DeviceCode NChar 							(10)  =  NULL 
														, @StratWorkingDate DateTime 							  =  NULL 
												AS
					UPDATE TechnicianDevice SET
													StratWorkingDate=@StratWorkingDate 
													Where 	
												TechnicianID=@TechnicianID
														AND DeviceCode=@DeviceCode
							'					

					PRINT 'dbo.SP_TechnicianDevice_Update done'
					go
				/*===========================End TechnicianDevice Update PROCEDURE=========================*/
 /*===========================Start TechnicianDevice Delete PROCEDURE===========================*/
 IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_TechnicianDevice_Delete') AND type in (N'P', N'PC'))
	BEGIN
		EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_TechnicianDevice_Delete AS'
	END
EXEC dbo.sp_executesql @statement = N'Alter PROCEDURE dbo.SP_TechnicianDevice_Delete
		@TechnicianID Int 		  =  NULL 
				, @DeviceCode NChar 					(10)  =  NULL 
								AS
			Delete From TechnicianDevice
			Where 	
								(TechnicianID=@TechnicianID)
										AND(DeviceCode=@DeviceCode)
					'
			PRINT 'dbo.SP_TechnicianDevice_Delete done'
			go
/*===========================End TechnicianDevice Delete PROCEDURE===========================*/
/*===========================Start Roles Select PROCEDURE===========================*/
IF NOT  EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_Roles_SELECT') AND type in (N'P', N'PC'))
	BEGIN
		EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_Roles_SELECT AS'
	END 
EXEC dbo.sp_executesql @statement = N'Alter PROCEDURE dbo.SP_Roles_SELECT
@StartIndex INT = NULL,
@lenght INT =  NULL ,
@OrderColume nvarchar(256) = NULL
, @RolesID Int   =  NULL 
, @EmployeeID Int   =  NULL 
, @Role NChar (10)  =  NULL 
	AS
			WITH CTE_Roles AS
			( 
			SELECT
 							RolesID,
								EmployeeID,
								Role,
							ROW_NUMBER() OVER (ORDER BY 
        	CASE 
									WHEN @OrderColume=''RolesID'' THEN RolesID
												WHEN @OrderColume=''EmployeeID'' THEN EmployeeID
						 
						WHEN @OrderColume=''Role'' THEN Role
									ELSE 
							    RolesID
					        	END
			) AS "RowNumber"
  			FROM Roles 
			where 
								(RolesID = @RolesID OR @RolesID Is Null)
										AND(EmployeeID = @EmployeeID OR @EmployeeID Is Null)
										AND(Role LIKE @Role OR @Role Is Null)
								)
			SELECT  *
			FROM CTE_Roles
			WHERE  (RowNumber >= @StartIndex OR @StartIndex Is Null) AND (RowNumber <= @StartIndex+@lenght OR (@StartIndex Is Null and @lenght Is Null))
			Return (select @@ROWCOUNT)'
			PRINT 'dbo.SP_Roles_SELECT done'
			go
 /*===========================End Roles Select PROCEDURE===========================*/
/*===========================Start Roles Insert PROCEDURE===========================*/
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_Roles_Insert') AND type in (N'P', N'PC'))
	Begin
		EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_Roles_Insert AS'
	END
	EXEC dbo.sp_executesql @statement = N'Alter PROCEDURE dbo.SP_Roles_Insert
	@RolesID Int   =  NULL 
		, @EmployeeID Int   =  NULL 
		, @Role NChar (10)  =  NULL 
		AS
			INSERT INTO Roles
			(
									RolesID
												,EmployeeID
												,Role
									)
			Values(
									@RolesID
												,@EmployeeID
												,@Role
						)
			return @@identity'
			PRINT 'dbo.SP_Roles_Insert done'
			go
/*===========================End Roles Insert PROCEDURE===========================*/
/*===========================Start Roles Update PROCEDURE=========================*/
		IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_Roles_UPDATE') AND type in (N'P', N'PC'))
		Begin
			EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_Roles_UPDATE AS'
		end
		EXEC dbo.sp_executesql @statement = N'ALTER PROCEDURE dbo.SP_Roles_UPDATE
												@RolesID Int 							  =  NULL 
														, @EmployeeID Int 							  =  NULL 
														, @Role NChar 							(10)  =  NULL 
												AS
					UPDATE Roles SET
													EmployeeID=@EmployeeID 
																,Role=@Role
													Where 	
												RolesID=@RolesID
							'					

					PRINT 'dbo.SP_Roles_Update done'
					go
				/*===========================End Roles Update PROCEDURE=========================*/
 /*===========================Start Roles Delete PROCEDURE===========================*/
 IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_Roles_Delete') AND type in (N'P', N'PC'))
	BEGIN
		EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_Roles_Delete AS'
	END
EXEC dbo.sp_executesql @statement = N'Alter PROCEDURE dbo.SP_Roles_Delete
		@RolesID Int 		  =  NULL 
					AS
			Delete From Roles
			Where 	
								(RolesID=@RolesID)
					'
			PRINT 'dbo.SP_Roles_Delete done'
			go
/*===========================End Roles Delete PROCEDURE===========================*/
/*===========================Start RadiologyReport Select PROCEDURE===========================*/
IF NOT  EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_RadiologyReport_SELECT') AND type in (N'P', N'PC'))
	BEGIN
		EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_RadiologyReport_SELECT AS'
	END 
EXEC dbo.sp_executesql @statement = N'Alter PROCEDURE dbo.SP_RadiologyReport_SELECT
@StartIndex INT = NULL,
@lenght INT =  NULL ,
@OrderColume nvarchar(256) = NULL
, @ReportID Int   =  NULL 
, @reportDate DateTime   =  NULL 
, @report NVarChar (Max)  =  NULL 
, @RequestID Int   =  NULL 
, @DoctorID Int   =  NULL 
	AS
			WITH CTE_RadiologyReport AS
			( 
			SELECT
 							ReportID,
								reportDate,
								report,
								RequestID,
								DoctorID,
							ROW_NUMBER() OVER (ORDER BY 
        	CASE 
									WHEN @OrderColume=''ReportID'' THEN ReportID
												WHEN @OrderColume=''reportDate'' THEN reportDate
						 
						WHEN @OrderColume=''report'' THEN report
												WHEN @OrderColume=''RequestID'' THEN RequestID
												WHEN @OrderColume=''DoctorID'' THEN DoctorID
									ELSE 
							    ReportID
					        	END
			) AS "RowNumber"
  			FROM RadiologyReport 
			where 
								(ReportID = @ReportID OR @ReportID Is Null)
										AND(reportDate = @reportDate OR @reportDate Is Null)
										AND(report LIKE @report OR @report Is Null)
										AND(RequestID = @RequestID OR @RequestID Is Null)
										AND(DoctorID = @DoctorID OR @DoctorID Is Null)
								)
			SELECT  *
			FROM CTE_RadiologyReport
			WHERE  (RowNumber >= @StartIndex OR @StartIndex Is Null) AND (RowNumber <= @StartIndex+@lenght OR (@StartIndex Is Null and @lenght Is Null))
			Return (select @@ROWCOUNT)'
			PRINT 'dbo.SP_RadiologyReport_SELECT done'
			go
 /*===========================End RadiologyReport Select PROCEDURE===========================*/
/*===========================Start RadiologyReport Insert PROCEDURE===========================*/
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_RadiologyReport_Insert') AND type in (N'P', N'PC'))
	Begin
		EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_RadiologyReport_Insert AS'
	END
	EXEC dbo.sp_executesql @statement = N'Alter PROCEDURE dbo.SP_RadiologyReport_Insert
	@reportDate DateTime   =  NULL 
		, @report NVarChar (Max)  =  NULL 
		, @RequestID Int   =  NULL 
		, @DoctorID Int   =  NULL 
		AS
			INSERT INTO RadiologyReport
			(
									reportDate
												,report
												,RequestID
												,DoctorID
									)
			Values(
									@reportDate
												,@report
												,@RequestID
												,@DoctorID
						)
			return @@identity'
			PRINT 'dbo.SP_RadiologyReport_Insert done'
			go
/*===========================End RadiologyReport Insert PROCEDURE===========================*/
/*===========================Start RadiologyReport Update PROCEDURE=========================*/
		IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_RadiologyReport_UPDATE') AND type in (N'P', N'PC'))
		Begin
			EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_RadiologyReport_UPDATE AS'
		end
		EXEC dbo.sp_executesql @statement = N'ALTER PROCEDURE dbo.SP_RadiologyReport_UPDATE
												@ReportID Int 							  =  NULL 
														, @reportDate DateTime 							  =  NULL 
														, @report NVarChar (Max)							  =  NULL 
														, @RequestID Int 							  =  NULL 
														, @DoctorID Int 							  =  NULL 
												AS
					UPDATE RadiologyReport SET
													reportDate=@reportDate 
																,report=@report
																,RequestID=@RequestID
																,DoctorID=@DoctorID
													Where 	
												ReportID=@ReportID
							'					

					PRINT 'dbo.SP_RadiologyReport_Update done'
					go
				/*===========================End RadiologyReport Update PROCEDURE=========================*/
 /*===========================Start RadiologyReport Delete PROCEDURE===========================*/
 IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_RadiologyReport_Delete') AND type in (N'P', N'PC'))
	BEGIN
		EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_RadiologyReport_Delete AS'
	END
EXEC dbo.sp_executesql @statement = N'Alter PROCEDURE dbo.SP_RadiologyReport_Delete
		@ReportID Int 		  =  NULL 
					AS
			Delete From RadiologyReport
			Where 	
								(ReportID=@ReportID)
					'
			PRINT 'dbo.SP_RadiologyReport_Delete done'
			go
/*===========================End RadiologyReport Delete PROCEDURE===========================*/
/*===========================Start Departments Select PROCEDURE===========================*/
IF NOT  EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_Departments_SELECT') AND type in (N'P', N'PC'))
	BEGIN
		EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_Departments_SELECT AS'
	END 
EXEC dbo.sp_executesql @statement = N'Alter PROCEDURE dbo.SP_Departments_SELECT
@StartIndex INT = NULL,
@lenght INT =  NULL ,
@OrderColume nvarchar(256) = NULL
, @Dep_id Int   =  NULL 
, @Name NVarChar (50)  =  NULL 
, @MangerStartDate DateTime   =  NULL 
, @MangerID Int   =  NULL 
	AS
			WITH CTE_Departments AS
			( 
			SELECT
 							Dep_id,
								Name,
								MangerStartDate,
								MangerID,
							ROW_NUMBER() OVER (ORDER BY 
        	CASE 
									WHEN @OrderColume=''Dep_id'' THEN Dep_id
						 
						WHEN @OrderColume=''Name'' THEN Name
												WHEN @OrderColume=''MangerStartDate'' THEN MangerStartDate
												WHEN @OrderColume=''MangerID'' THEN MangerID
									ELSE 
							    Dep_id
					        	END
			) AS "RowNumber"
  			FROM Departments 
			where 
								(Dep_id = @Dep_id OR @Dep_id Is Null)
										AND(Name LIKE @Name OR @Name Is Null)
										AND(MangerStartDate = @MangerStartDate OR @MangerStartDate Is Null)
										AND(MangerID = @MangerID OR @MangerID Is Null)
								)
			SELECT  *
			FROM CTE_Departments
			WHERE  (RowNumber >= @StartIndex OR @StartIndex Is Null) AND (RowNumber <= @StartIndex+@lenght OR (@StartIndex Is Null and @lenght Is Null))
			Return (select @@ROWCOUNT)'
			PRINT 'dbo.SP_Departments_SELECT done'
			go
 /*===========================End Departments Select PROCEDURE===========================*/
/*===========================Start Departments Insert PROCEDURE===========================*/
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_Departments_Insert') AND type in (N'P', N'PC'))
	Begin
		EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_Departments_Insert AS'
	END
	EXEC dbo.sp_executesql @statement = N'Alter PROCEDURE dbo.SP_Departments_Insert
	@Name NVarChar (50)  =  NULL 
		, @MangerStartDate DateTime   =  NULL 
		, @MangerID Int   =  NULL 
		AS
			INSERT INTO Departments
			(
									Name
												,MangerStartDate
												,MangerID
									)
			Values(
									@Name
												,@MangerStartDate
												,@MangerID
						)
			return @@identity'
			PRINT 'dbo.SP_Departments_Insert done'
			go
/*===========================End Departments Insert PROCEDURE===========================*/
/*===========================Start Departments Update PROCEDURE=========================*/
		IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_Departments_UPDATE') AND type in (N'P', N'PC'))
		Begin
			EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_Departments_UPDATE AS'
		end
		EXEC dbo.sp_executesql @statement = N'ALTER PROCEDURE dbo.SP_Departments_UPDATE
												@Dep_id Int 							  =  NULL 
														, @Name NVarChar 							(50)  =  NULL 
														, @MangerStartDate DateTime 							  =  NULL 
														, @MangerID Int 							  =  NULL 
												AS
					UPDATE Departments SET
													Name=@Name 
																,MangerStartDate=@MangerStartDate
																,MangerID=@MangerID
													Where 	
												Dep_id=@Dep_id
							'					

					PRINT 'dbo.SP_Departments_Update done'
					go
				/*===========================End Departments Update PROCEDURE=========================*/
 /*===========================Start Departments Delete PROCEDURE===========================*/
 IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_Departments_Delete') AND type in (N'P', N'PC'))
	BEGIN
		EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_Departments_Delete AS'
	END
EXEC dbo.sp_executesql @statement = N'Alter PROCEDURE dbo.SP_Departments_Delete
		@Dep_id Int 		  =  NULL 
					AS
			Delete From Departments
			Where 	
								(Dep_id=@Dep_id)
					'
			PRINT 'dbo.SP_Departments_Delete done'
			go
/*===========================End Departments Delete PROCEDURE===========================*/
/*===========================Start Employees Select PROCEDURE===========================*/
IF NOT  EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_Employees_SELECT') AND type in (N'P', N'PC'))
	BEGIN
		EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_Employees_SELECT AS'
	END 
EXEC dbo.sp_executesql @statement = N'Alter PROCEDURE dbo.SP_Employees_SELECT
@StartIndex INT = NULL,
@lenght INT =  NULL ,
@OrderColume nvarchar(256) = NULL
, @Employee_id Int   =  NULL 
, @FirstName NVarChar (100)  =  NULL 
, @Mobile NVarChar (50)  =  NULL 
, @Address NVarChar (200)  =  NULL 
, @Email NVarChar (50)  =  NULL 
, @gender Bit   =  NULL 
, @NameTitle NChar (10)  =  NULL 
, @Dep_id Int   =  NULL 
, @Username NVarChar (50)  =  NULL 
, @IsActive Bit   =  NULL 
, @Password NVarChar (50)  =  NULL 
, @FamilyName NVarChar (100)  =  NULL 
, @Phone NChar (10)  =  NULL 
	AS
			WITH CTE_Employees AS
			( 
			SELECT
 							Employee_id,
								FirstName,
								Mobile,
								Address,
								Email,
								gender,
								NameTitle,
								Dep_id,
								Username,
								IsActive,
								Password,
								FamilyName,
								Phone,
							ROW_NUMBER() OVER (ORDER BY 
        	CASE 
									WHEN @OrderColume=''Employee_id'' THEN Employee_id
						 
						WHEN @OrderColume=''FirstName'' THEN FirstName
						 
						WHEN @OrderColume=''Mobile'' THEN Mobile
						 
						WHEN @OrderColume=''Address'' THEN Address
						 
						WHEN @OrderColume=''Email'' THEN Email
												WHEN @OrderColume=''gender'' THEN gender
						 
						WHEN @OrderColume=''NameTitle'' THEN NameTitle
												WHEN @OrderColume=''Dep_id'' THEN Dep_id
						 
						WHEN @OrderColume=''Username'' THEN Username
												WHEN @OrderColume=''IsActive'' THEN IsActive
						 
						WHEN @OrderColume=''Password'' THEN Password
						 
						WHEN @OrderColume=''FamilyName'' THEN FamilyName
						 
						WHEN @OrderColume=''Phone'' THEN Phone
									ELSE 
							    Employee_id
					        	END
			) AS "RowNumber"
  			FROM Employees 
			where 
								(Employee_id = @Employee_id OR @Employee_id Is Null)
										AND(FirstName LIKE @FirstName OR @FirstName Is Null)
										AND(Mobile LIKE @Mobile OR @Mobile Is Null)
										AND(Address LIKE @Address OR @Address Is Null)
										AND(Email LIKE @Email OR @Email Is Null)
										AND(gender = @gender OR @gender Is Null)
										AND(NameTitle LIKE @NameTitle OR @NameTitle Is Null)
										AND(Dep_id = @Dep_id OR @Dep_id Is Null)
										AND(Username LIKE @Username OR @Username Is Null)
										AND(IsActive = @IsActive OR @IsActive Is Null)
										AND(Password LIKE @Password OR @Password Is Null)
										AND(FamilyName LIKE @FamilyName OR @FamilyName Is Null)
										AND(Phone LIKE @Phone OR @Phone Is Null)
								)
			SELECT  *
			FROM CTE_Employees
			WHERE  (RowNumber >= @StartIndex OR @StartIndex Is Null) AND (RowNumber <= @StartIndex+@lenght OR (@StartIndex Is Null and @lenght Is Null))
			Return (select @@ROWCOUNT)'
			PRINT 'dbo.SP_Employees_SELECT done'
			go
 /*===========================End Employees Select PROCEDURE===========================*/
/*===========================Start Employees Insert PROCEDURE===========================*/
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_Employees_Insert') AND type in (N'P', N'PC'))
	Begin
		EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_Employees_Insert AS'
	END
	EXEC dbo.sp_executesql @statement = N'Alter PROCEDURE dbo.SP_Employees_Insert
	@FirstName NVarChar (100)  =  NULL 
		, @Mobile NVarChar (50)  =  NULL 
		, @Address NVarChar (200)  =  NULL 
		, @Email NVarChar (50)  =  NULL 
		, @gender Bit   =  NULL 
		, @NameTitle NChar (10)  =  NULL 
		, @Dep_id Int   =  NULL 
		, @Username NVarChar (50)  =  NULL 
		, @IsActive Bit   =  NULL 
		, @Password NVarChar (50)  =  NULL 
		, @FamilyName NVarChar (100)  =  NULL 
		, @Phone NChar (10)  =  NULL 
		AS
			INSERT INTO Employees
			(
									FirstName
												,Mobile
												,Address
												,Email
												,gender
												,NameTitle
												,Dep_id
												,Username
												,IsActive
												,Password
												,FamilyName
												,Phone
									)
			Values(
									@FirstName
												,@Mobile
												,@Address
												,@Email
												,@gender
												,@NameTitle
												,@Dep_id
												,@Username
												,@IsActive
												,@Password
												,@FamilyName
												,@Phone
						)
			return @@identity'
			PRINT 'dbo.SP_Employees_Insert done'
			go
/*===========================End Employees Insert PROCEDURE===========================*/
/*===========================Start Employees Update PROCEDURE=========================*/
		IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_Employees_UPDATE') AND type in (N'P', N'PC'))
		Begin
			EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_Employees_UPDATE AS'
		end
		EXEC dbo.sp_executesql @statement = N'ALTER PROCEDURE dbo.SP_Employees_UPDATE
												@Employee_id Int 							  =  NULL 
														, @FirstName NVarChar 							(100)  =  NULL 
														, @Mobile NVarChar 							(50)  =  NULL 
														, @Address NVarChar 							(200)  =  NULL 
														, @Email NVarChar 							(50)  =  NULL 
														, @gender Bit 							  =  NULL 
														, @NameTitle NChar 							(10)  =  NULL 
														, @Dep_id Int 							  =  NULL 
														, @Username NVarChar 							(50)  =  NULL 
														, @IsActive Bit 							  =  NULL 
														, @Password NVarChar 							(50)  =  NULL 
														, @FamilyName NVarChar 							(100)  =  NULL 
														, @Phone NChar 							(10)  =  NULL 
												AS
					UPDATE Employees SET
													FirstName=@FirstName 
																,Mobile=@Mobile
																,Address=@Address
																,Email=@Email
																,gender=@gender
																,NameTitle=@NameTitle
																,Dep_id=@Dep_id
																,Username=@Username
																,IsActive=@IsActive
																,Password=@Password
																,FamilyName=@FamilyName
																,Phone=@Phone
													Where 	
												Employee_id=@Employee_id
							'					

					PRINT 'dbo.SP_Employees_Update done'
					go
				/*===========================End Employees Update PROCEDURE=========================*/
 /*===========================Start Employees Delete PROCEDURE===========================*/
 IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_Employees_Delete') AND type in (N'P', N'PC'))
	BEGIN
		EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_Employees_Delete AS'
	END
EXEC dbo.sp_executesql @statement = N'Alter PROCEDURE dbo.SP_Employees_Delete
		@Employee_id Int 		  =  NULL 
					AS
			Delete From Employees
			Where 	
								(Employee_id=@Employee_id)
					'
			PRINT 'dbo.SP_Employees_Delete done'
			go
/*===========================End Employees Delete PROCEDURE===========================*/
/*===========================Start Patients Select PROCEDURE===========================*/
IF NOT  EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_Patients_SELECT') AND type in (N'P', N'PC'))
	BEGIN
		EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_Patients_SELECT AS'
	END 
EXEC dbo.sp_executesql @statement = N'Alter PROCEDURE dbo.SP_Patients_SELECT
@StartIndex INT = NULL,
@lenght INT =  NULL ,
@OrderColume nvarchar(256) = NULL
, @PatientID Int   =  NULL 
, @FirstName NVarChar (50)  =  NULL 
, @Gender Bit   =  NULL 
, @Birthday DateTime   =  NULL 
, @Nationality NVarChar (50)  =  NULL 
, @Phone NVarChar (50)  =  NULL 
, @Mobile NVarChar (50)  =  NULL 
, @Address NVarChar (200)  =  NULL 
, @ContractID Int   =  NULL 
, @FamilyName NVarChar (50)  =  NULL 
	AS
			WITH CTE_Patients AS
			( 
			SELECT
 							PatientID,
								FirstName,
								Gender,
								Birthday,
								Nationality,
								Phone,
								Mobile,
								Address,
								ContractID,
								FamilyName,
							ROW_NUMBER() OVER (ORDER BY 
        	CASE 
									WHEN @OrderColume=''PatientID'' THEN PatientID
						 
						WHEN @OrderColume=''FirstName'' THEN FirstName
												WHEN @OrderColume=''Gender'' THEN Gender
												WHEN @OrderColume=''Birthday'' THEN Birthday
						 
						WHEN @OrderColume=''Nationality'' THEN Nationality
						 
						WHEN @OrderColume=''Phone'' THEN Phone
						 
						WHEN @OrderColume=''Mobile'' THEN Mobile
						 
						WHEN @OrderColume=''Address'' THEN Address
												WHEN @OrderColume=''ContractID'' THEN ContractID
						 
						WHEN @OrderColume=''FamilyName'' THEN FamilyName
									ELSE 
							    PatientID
					        	END
			) AS "RowNumber"
  			FROM Patients 
			where 
								(PatientID = @PatientID OR @PatientID Is Null)
										AND(FirstName LIKE @FirstName OR @FirstName Is Null)
										AND(Gender = @Gender OR @Gender Is Null)
										AND(Birthday = @Birthday OR @Birthday Is Null)
										AND(Nationality LIKE @Nationality OR @Nationality Is Null)
										AND(Phone LIKE @Phone OR @Phone Is Null)
										AND(Mobile LIKE @Mobile OR @Mobile Is Null)
										AND(Address LIKE @Address OR @Address Is Null)
										AND(ContractID = @ContractID OR @ContractID Is Null)
										AND(FamilyName LIKE @FamilyName OR @FamilyName Is Null)
								)
			SELECT  *
			FROM CTE_Patients
			WHERE  (RowNumber >= @StartIndex OR @StartIndex Is Null) AND (RowNumber <= @StartIndex+@lenght OR (@StartIndex Is Null and @lenght Is Null))
			Return (select @@ROWCOUNT)'
			PRINT 'dbo.SP_Patients_SELECT done'
			go
 /*===========================End Patients Select PROCEDURE===========================*/
/*===========================Start Patients Insert PROCEDURE===========================*/
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_Patients_Insert') AND type in (N'P', N'PC'))
	Begin
		EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_Patients_Insert AS'
	END
	EXEC dbo.sp_executesql @statement = N'Alter PROCEDURE dbo.SP_Patients_Insert
	@FirstName NVarChar (50)  =  NULL 
		, @Gender Bit   =  NULL 
		, @Birthday DateTime   =  NULL 
		, @Nationality NVarChar (50)  =  NULL 
		, @Phone NVarChar (50)  =  NULL 
		, @Mobile NVarChar (50)  =  NULL 
		, @Address NVarChar (200)  =  NULL 
		, @ContractID Int   =  NULL 
		, @FamilyName NVarChar (50)  =  NULL 
		AS
			INSERT INTO Patients
			(
									FirstName
												,Gender
												,Birthday
												,Nationality
												,Phone
												,Mobile
												,Address
												,ContractID
												,FamilyName
									)
			Values(
									@FirstName
												,@Gender
												,@Birthday
												,@Nationality
												,@Phone
												,@Mobile
												,@Address
												,@ContractID
												,@FamilyName
						)
			return @@identity'
			PRINT 'dbo.SP_Patients_Insert done'
			go
/*===========================End Patients Insert PROCEDURE===========================*/
/*===========================Start Patients Update PROCEDURE=========================*/
		IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_Patients_UPDATE') AND type in (N'P', N'PC'))
		Begin
			EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_Patients_UPDATE AS'
		end
		EXEC dbo.sp_executesql @statement = N'ALTER PROCEDURE dbo.SP_Patients_UPDATE
												@PatientID Int 							  =  NULL 
														, @FirstName NVarChar 							(50)  =  NULL 
														, @Gender Bit 							  =  NULL 
														, @Birthday DateTime 							  =  NULL 
														, @Nationality NVarChar 							(50)  =  NULL 
														, @Phone NVarChar 							(50)  =  NULL 
														, @Mobile NVarChar 							(50)  =  NULL 
														, @Address NVarChar 							(200)  =  NULL 
														, @ContractID Int 							  =  NULL 
														, @FamilyName NVarChar 							(50)  =  NULL 
												AS
					UPDATE Patients SET
													FirstName=@FirstName 
																,Gender=@Gender
																,Birthday=@Birthday
																,Nationality=@Nationality
																,Phone=@Phone
																,Mobile=@Mobile
																,Address=@Address
																,ContractID=@ContractID
																,FamilyName=@FamilyName
													Where 	
												PatientID=@PatientID
							'					

					PRINT 'dbo.SP_Patients_Update done'
					go
				/*===========================End Patients Update PROCEDURE=========================*/
 /*===========================Start Patients Delete PROCEDURE===========================*/
 IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_Patients_Delete') AND type in (N'P', N'PC'))
	BEGIN
		EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_Patients_Delete AS'
	END
EXEC dbo.sp_executesql @statement = N'Alter PROCEDURE dbo.SP_Patients_Delete
		@PatientID Int 		  =  NULL 
					AS
			Delete From Patients
			Where 	
								(PatientID=@PatientID)
					'
			PRINT 'dbo.SP_Patients_Delete done'
			go
/*===========================End Patients Delete PROCEDURE===========================*/

/***************************** Strart View Select PROCEDURE********************************************/
/*===========================Start DoctorRequests Select PROCEDURE===========================*/
IF NOT  EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_DoctorRequestsDTO_SELECT') AND type in (N'P', N'PC'))
	BEGIN
		EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_DoctorRequestsDTO_SELECT AS '
	END 
EXEC dbo.sp_executesql @statement = N'Alter PROCEDURE dbo.SP_DoctorRequestsDTO_SELECT
@StartIndex INT = NULL,
@lenght INT =  NULL ,
@OrderColume nvarchar(256) = NULL

, @DeviceCode NChar (10)  =  NULL 


, @Dep_id Int   =  NULL 


, @RadiologyCode NChar (10)  =  NULL 


, @Description NVarChar (500)  =  NULL 


, @RequestId Int   =  NULL 


, @RequestDate DateTime   =  NULL 


, @RequestStatues Bit   =  NULL 


, @PatientID Int   =  NULL 


, @ReportID Int   =  NULL 


, @Employee_id Int   =  NULL 


, @FirstName NVarChar (50)  =  NULL 


, @FamilyName NVarChar (50)  =  NULL 

	AS
			WITH CTE_DoctorRequests AS
			( 
			SELECT
 							v.DeviceCode,
								v.Dep_id,
								v.RadiologyCode,
								v.Description,
								v.RequestId,
								v.RequestDate,
								v.RequestStatues,
								v.PatientID,
								v.ReportID,
								v.Employee_id,
								v.FirstName,
								v.FamilyName,
							ROW_NUMBER() OVER (ORDER BY 
        	CASE 
			 
						WHEN @OrderColume=''DeviceCode'' THEN v.DeviceCode
												WHEN @OrderColume=''Dep_id'' THEN v.Dep_id
						 
						WHEN @OrderColume=''RadiologyCode'' THEN v.RadiologyCode
						 
						WHEN @OrderColume=''Description'' THEN v.Description
												WHEN @OrderColume=''RequestId'' THEN v.RequestId
												WHEN @OrderColume=''RequestDate'' THEN v.RequestDate
												WHEN @OrderColume=''RequestStatues'' THEN v.RequestStatues
												WHEN @OrderColume=''PatientID'' THEN v.PatientID
												WHEN @OrderColume=''ReportID'' THEN v.ReportID
												WHEN @OrderColume=''Employee_id'' THEN v.Employee_id
						 
						WHEN @OrderColume=''FirstName'' THEN v.FirstName
						 
						WHEN @OrderColume=''FamilyName'' THEN v.FamilyName
						        	END
			) AS "RowNumber"
  			FROM DoctorRequests v 
			where 
								(DeviceCode=@DeviceCode OR @DeviceCode Is Null)
										AND(Dep_id=@Dep_id OR @Dep_id Is Null)
										AND(RadiologyCode=@RadiologyCode OR @RadiologyCode Is Null)
										AND(Description=@Description OR @Description Is Null)
										AND(RequestId=@RequestId OR @RequestId Is Null)
										AND(RequestDate=@RequestDate OR @RequestDate Is Null)
										AND(RequestStatues=@RequestStatues OR @RequestStatues Is Null)
										AND(PatientID=@PatientID OR @PatientID Is Null)
										AND(ReportID=@ReportID OR @ReportID Is Null)
										AND(Employee_id=@Employee_id OR @Employee_id Is Null)
										AND(FirstName=@FirstName OR @FirstName Is Null)
										AND(FamilyName=@FamilyName OR @FamilyName Is Null)
								)
			SELECT *
			FROM CTE_DoctorRequests
			WHERE  (RowNumber >= @StartIndex OR @StartIndex Is Null) AND (RowNumber <= @StartIndex+@lenght OR (@StartIndex Is Null and @lenght Is Null))
			Return (
			SELECT Count(*) AS TotalRecords 
            FROM DoctorRequests v
            where 
								(v.DeviceCode=@DeviceCode OR @DeviceCode Is Null)
										AND(v.Dep_id=@Dep_id OR @Dep_id Is Null)
										AND(v.RadiologyCode=@RadiologyCode OR @RadiologyCode Is Null)
										AND(v.Description=@Description OR @Description Is Null)
										AND(v.RequestId=@RequestId OR @RequestId Is Null)
										AND(v.RequestDate=@RequestDate OR @RequestDate Is Null)
										AND(v.RequestStatues=@RequestStatues OR @RequestStatues Is Null)
										AND(v.PatientID=@PatientID OR @PatientID Is Null)
										AND(v.ReportID=@ReportID OR @ReportID Is Null)
										AND(v.Employee_id=@Employee_id OR @Employee_id Is Null)
										AND(v.FirstName=@FirstName OR @FirstName Is Null)
										AND(v.FamilyName=@FamilyName OR @FamilyName Is Null)
					 	
			)'
			PRINT 'dbo.SP_DoctorRequestsDTO_SELECT done'
			go
 /*===========================End DoctorRequests Select PROCEDURE===========================*/
/*===========================Start TechnicianRadiologyRequests Select PROCEDURE===========================*/
IF NOT  EXISTS(SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SP_TechnicianRadiologyRequestsDTO_SELECT') AND type in (N'P', N'PC'))
	BEGIN
		EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SP_TechnicianRadiologyRequestsDTO_SELECT AS '
	END 
EXEC dbo.sp_executesql @statement = N'Alter PROCEDURE dbo.SP_TechnicianRadiologyRequestsDTO_SELECT
@StartIndex INT = NULL,
@lenght INT =  NULL ,
@OrderColume nvarchar(256) = NULL

, @DeviceCode NChar (10)  =  NULL 


, @DeviceName NVarChar (100)  =  NULL 


, @RadiologyCode NChar (10)  =  NULL 


, @Description NVarChar (500)  =  NULL 


, @RequestId Int   =  NULL 


, @RequestDate DateTime   =  NULL 


, @ReservationDate DateTime   =  NULL 


, @RequestStatues Bit   =  NULL 


, @PatientID Int   =  NULL 


, @TechnicianID Int   =  NULL 


, @FirstName NVarChar (50)  =  NULL 


, @FamilyName NVarChar (50)  =  NULL 

	AS
			WITH CTE_TechnicianRadiologyRequests AS
			( 
			SELECT
 							v.DeviceCode,
								v.DeviceName,
								v.RadiologyCode,
								v.Description,
								v.RequestId,
								v.RequestDate,
								v.ReservationDate,
								v.RequestStatues,
								v.PatientID,
								v.TechnicianID,
								v.FirstName,
								v.FamilyName,
							ROW_NUMBER() OVER (ORDER BY 
        	CASE 
			 
						WHEN @OrderColume=''DeviceCode'' THEN v.DeviceCode
						 
						WHEN @OrderColume=''DeviceName'' THEN v.DeviceName
						 
						WHEN @OrderColume=''RadiologyCode'' THEN v.RadiologyCode
						 
						WHEN @OrderColume=''Description'' THEN v.Description
												WHEN @OrderColume=''RequestId'' THEN v.RequestId
												WHEN @OrderColume=''RequestDate'' THEN v.RequestDate
												WHEN @OrderColume=''ReservationDate'' THEN v.ReservationDate
												WHEN @OrderColume=''RequestStatues'' THEN v.RequestStatues
												WHEN @OrderColume=''PatientID'' THEN v.PatientID
												WHEN @OrderColume=''TechnicianID'' THEN v.TechnicianID
						 
						WHEN @OrderColume=''FirstName'' THEN v.FirstName
						 
						WHEN @OrderColume=''FamilyName'' THEN v.FamilyName
						        	END
			) AS "RowNumber"
  			FROM TechnicianRadiologyRequests v 
			where 
								(DeviceCode=@DeviceCode OR @DeviceCode Is Null)
										AND(DeviceName=@DeviceName OR @DeviceName Is Null)
										AND(RadiologyCode=@RadiologyCode OR @RadiologyCode Is Null)
										AND(Description=@Description OR @Description Is Null)
										AND(RequestId=@RequestId OR @RequestId Is Null)
										AND(RequestDate=@RequestDate OR @RequestDate Is Null)
										AND(ReservationDate=@ReservationDate OR @ReservationDate Is Null)
										AND(RequestStatues=@RequestStatues OR @RequestStatues Is Null)
										AND(PatientID=@PatientID OR @PatientID Is Null)
										AND(TechnicianID=@TechnicianID OR @TechnicianID Is Null)
										AND(FirstName=@FirstName OR @FirstName Is Null)
										AND(FamilyName=@FamilyName OR @FamilyName Is Null)
								)
			SELECT *
			FROM CTE_TechnicianRadiologyRequests
			WHERE  (RowNumber >= @StartIndex OR @StartIndex Is Null) AND (RowNumber <= @StartIndex+@lenght OR (@StartIndex Is Null and @lenght Is Null))
			Return (
			SELECT Count(*) AS TotalRecords 
            FROM TechnicianRadiologyRequests v
            where 
								(v.DeviceCode=@DeviceCode OR @DeviceCode Is Null)
										AND(v.DeviceName=@DeviceName OR @DeviceName Is Null)
										AND(v.RadiologyCode=@RadiologyCode OR @RadiologyCode Is Null)
										AND(v.Description=@Description OR @Description Is Null)
										AND(v.RequestId=@RequestId OR @RequestId Is Null)
										AND(v.RequestDate=@RequestDate OR @RequestDate Is Null)
										AND(v.ReservationDate=@ReservationDate OR @ReservationDate Is Null)
										AND(v.RequestStatues=@RequestStatues OR @RequestStatues Is Null)
										AND(v.PatientID=@PatientID OR @PatientID Is Null)
										AND(v.TechnicianID=@TechnicianID OR @TechnicianID Is Null)
										AND(v.FirstName=@FirstName OR @FirstName Is Null)
										AND(v.FamilyName=@FamilyName OR @FamilyName Is Null)
					 	
			)'
			PRINT 'dbo.SP_TechnicianRadiologyRequestsDTO_SELECT done'
			go
 /*===========================End TechnicianRadiologyRequests Select PROCEDURE===========================*/
/*****************************End View Select PROCEDURE********************************************/


